package com.supermap.dataservice.cloud.china;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * @version v1.0.0
 * @Author:zhoujian
 * @Desciption:综合统计
 * @date 2019-05-07 15:17
 */
@Mapper
public interface SyntheticalStatisticsMapper {

    @Select("/*分区统计专家数量*/\n" +
            "select te.areaname , expert_count ,active_expert_count, user_count , advisory_count, fland_count,speciality_product_count ,site_user_count ,agri_count " +
            " ,expert_product_count from \n" +
            "(select if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname ,  count(t1.id) as expert_count from t_area a left join \n" +
            "(\n" +
            "\tselect t.id, u.areaCode , t.createtime from t_expertinfo t inner join t_user u on t.userid = u.id where t.createtime between #{startDate} and #{endDate}\n" +
            ")t1 on a.areacode = t1.areaCode\n" +
            "where a.areacode like concat( #{areaCode},\"%\")  group by  a.areaid ) te,\n" +
            "(\n" +
            "\t/*用户*/\n" +
            "\tselect if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname ,  count(t1.id) as user_count from t_area a \n" +
            "left join (select u.id,u.areaCode from  t_user u where u.createtime between #{startDate} and #{endDate}) t1  on t1.areaCode = a.areacode \n" +
            "where a.areaCode like concat(#{areaCode},\"%\")  group by a.areaid\n" +
            ")tu ,\n" +
            "(\n" +
            "/*咨询数量*/\n" +
            "\tSELECT IF(a.areaname=\"重庆市\",\"科研所\",a.areaname) AS areaname, COUNT(t1.id) AS advisory_count\n" +
            "\tFROM t_area a\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT t.id, t.areacode\n" +
            "\tFROM t_advisoryinfo t\n" +
            "\tWHERE t.uploadTime BETWEEN #{startDate} and #{endDate}) t1 ON t1.areacode = a.areacode\n" +
            "\tWHERE a.areaCode LIKE concat( #{areaCode},\"%\")\n" +
            "\tGROUP BY a.areaid\n" +
            ") ta,\n" +
            "(\n" +
            "\tSELECT if(a.areaname=\"重庆市\",\"科研所\",a.areaname) as areaname, COUNT(t1.id) AS fland_count\n" +
            "FROM t_area a\n" +
            "LEFT JOIN (select t.id,t.code from t_farmland t where t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.code\n" +
            "WHERE a.areacode LIKE concat( #{areaCode},\"%\") \n" +
            "GROUP BY a.areaid\n" +
            ") tf,\n" +
            "(\n" +
            "\t/*专业产品数量*/\n" +
            "\tSELECT IF(a.areaname='重庆市','科研所',a.areaname) AS areaname, COUNT(t1.id) AS speciality_product_count\n" +
            "\tFROM t_area a\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT t.id, t.areacode\n" +
            "\tFROM t_area_product t\n" +
            "\tWHERE t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.areacode\n" +
            "\tWHERE a.areacode LIKE concat( #{areaCode},\"%\")\n" +
            "\tGROUP BY a.areaid\n" +
            ") tap,\n" +
            "(\n" +
            "/*加法*/\n" +
            "\t/*分区统计建站用户数量*/\n" +
            "\tselect tt1.areaname , tt1.counts+tt2.counts  as site_user_count from \n" +
            "\t(\n" +
            "\t\tselect IF(a.areaname='重庆市','科研所',a.areaname) AS areaname , count(t1.id) as counts from t_area a left join \n" +
            "\t\t(\n" +
            "\t\tselect t.id , u.areaCode from  t_company t \n" +
            "\t\tinner join t_user u on t.userid = u.id \n" +
            "\t\twhere t.url is not null\n" +
            "\t\tand t.createtime between #{startDate} and #{endDate} \n" +
            "\t\t) t1 on a.areacode = t1.areaCode\n" +
            "\t\twhere a.areacode like concat( #{areaCode},\"%\")\n" +
            "\t\tgroup by a.areaid \n" +
            "\t) tt1,\n" +
            "\t(\n" +
            "\t\tselect IF(a.areaname='重庆市','科研所',a.areaname) AS areaname ,count(t2.id) as counts from t_area a left join \n" +
            "\t\t(\n" +
            "\t\tselect t.id , u.areaCode from  t_countryside t \n" +
            "\t\tinner join t_user u on t.userid = u.id \n" +
            "\t\twhere t.url is not null\n" +
            "\t\tand t.createtime between #{startDate} and #{endDate} \n" +
            "\t\t) t2 on a.areacode = t2.areaCode\n" +
            "\t\twhere a.areacode like concat( #{areaCode},\"%\")\n" +
            "\t\tgroup by a.areaid\n" +
            "\t) tt2 \n" +
            "\twhere tt1.areaname = tt2.areaname\n" +
            ") tc,\n" +
            "(\n" +
            "\t/*区域分组统计农情数量*/\n" +
            "\tSELECT IF(a.areaname='重庆市','科研所',a.areaname) AS areaname , COUNT(t1.id) AS agri_count\n" +
            "\tFROM t_area a\n" +
            "\tLEFT JOIN (\n" +
            "\tSELECT t.id, t.areacode\n" +
            "\tFROM t_agrinfo t\n" +
            "\tWHERE t.createtime BETWEEN #{startDate} and #{endDate}) t1 ON a.areacode = t1.areacode\n" +
            "\tWHERE a.areaCode LIKE concat( #{areaCode},\"%\")\n" +
            "\tGROUP BY a.areaid\n" +
            ") tag,\n" +
            "(\n" +
            "\t\n" +
            "select tx.areaname , ad_count+ag_count+su_count as active_expert_count from \n" +
            "(\n" +
            "\t/*统计专家咨询评论*/\n" +
            "\tselect if(a.areaname = \"重庆市\", \"科研所\",a.areaname) as areaname, count(ttt.id) as ad_count from t_area a left join \n" +
            "\t(\n" +
            "\t\tselect u.id , u.areaCode from t_expertinfo tx  inner join t_advinfocomment t \n" +
            "\t\ton tx.userid = t.userId\n" +
            "\t\tinner join t_user u \n" +
            "\t\ton tx.userid = u.id\n" +
            "\t\twhere t.commentTime between #{startDate} and #{endDate}\n" +
            "\t) ttt\n" +
            "\ton a.areacode = ttt.areaCode\n" +
            "\twhere a.areacode like concat( #{areaCode},\"%\")\n" +
            "\tgroup by a.areaid\n" +
            ") tx inner join \n" +
            "(\n" +
            "\t\t/*统计专家农情评论*/\n" +
            "\t\tselect if(a.areaname = \"重庆市\", \"科研所\",a.areaname) as areaname, count(ttt.id) as ag_count from t_area a left join \n" +
            "\t\t(\n" +
            "\t\t\tselect u.id , u.areaCode from t_expertinfo tx  inner join t_agrinfocomment t \n" +
            "\t\t\ton tx.userid = t.userId\n" +
            "\t\t\tinner join t_user u \n" +
            "\t\t\ton tx.userid = u.id\n" +
            "\t\t\twhere t.commentTime between #{startDate} and #{endDate}\n" +
            "\t\t) ttt\n" +
            "\t\ton a.areacode = ttt.areaCode\n" +
            "\t\twhere a.areacode like concat( #{areaCode},\"%\")\n" +
            "\t\tgroup by a.areaid\n" +
            "\t) tx2 on tx.areaname = tx2.areaname\n" +
            "\tinner join \n" +
            "\t(\n" +
            "\t\t/*专家建议*/\n" +
            "\t\tselect if(a.areaname=\"重庆市\",\"科研所\",a.areaname ) as areaname, count(ts.id) as su_count from t_area a left join \n" +
            "\t\t(select s.id , s.areacode from t_suggest s where s.stime between #{startDate} and #{endDate}) ts on a.areacode = ts.areacode\n" +
            "\t\twhere a.areacode  like concat( #{areaCode},\"%\")\n" +
            "\t\tgroup by a.areaid\n" +
            "\t) tx3 on tx2.areaname = tx3.areaname\n" +
            ") tae,\n" +
            "(\n" +
            "\t/*专家产品*/\n" +
            "\tselect if(a.areaname=\"重庆市\",\"科研所\",a.areaname ) as areaname, count(ts.id) as expert_product_count from t_area a left join \n" +
            "\t(select s.id , s.areacode from t_suggest s where s.stime between #{startDate} and #{endDate}) ts on a.areacode = ts.areacode\n" +
            "\twhere a.areacode  like concat( #{areaCode},\"%\")\n" +
            "\tgroup by a.areaid\n" +
            ") tsp\n" +
            "where te.areaname = tu.areaname\n" +
            "and te.areaname = ta.areaname\n" +
            "and te.areaname = tf.areaname\n" +
            "and te.areaname = tap.areaname\n" +
            "and te.areaname = tc.areaname\n" +
            "and te.areaname = tag.areaname\n" +
            "and te.areaname = tae.areaname\n" +
            "and te.areaname = tsp.areaname")
    List<Map<String,Object>> SyntheticalStatistics(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);








    @Select("SELECT IF(t4.areaname='重庆市','科研所',t4.areaname) AS areaname, COUNT(t5.id) AS public_product_count\n" +
            "FROM t_area t4\n" +
            "LEFT JOIN (\n" +
            "select tt.id, t3.depareacode,tt.create_date , tt.product_maker from (\n" +
            "\tSELECT t1.id,  t1.create_date , t1.product_maker\n" +
            "\tFROM t_b_pms_product t1 where  t1.create_date BETWEEN #{startDate} AND #{endDate}) tt\n" +
            "INNER JOIN t_s_user_org t2 ON tt.product_maker = t2.user_id\n" +
            "INNER JOIN t_s_depart t3 ON t2.org_id = t3.ID)t5 ON t4.areacode = t5.depareacode\n" +
            "WHERE t4.areacode LIKE CONCAT(#{areaCode},\"%\")\n" +
            "GROUP BY t4.areaid")
    List<Map<String,Object>> publicProductStatistics(@Param("areaCode") String areaCode , @Param("startDate") String startDate , @Param("endDate") String endDate);




}